package base;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.ResourceBundle;
import java.util.TimeZone;

import sosr.bean.*;


public class Base {

	String fichierConfig = "base";
	String url = null;
	String user = null;
	String password = null;
	
	Connection connection = null;
	
	public Base() {
		ResourceBundle rs = 
			ResourceBundle.getBundle(fichierConfig);
		url = rs.getString("url");
		user = rs.getString("user");
		password = rs.getString("password");
	}
	
	public Connection getConnection() {
		return connection;
	}

	public void ouvrir() {
		try {
			connection = DriverManager.getConnection(url, user, password);
			//GUI.getInstance().setConsolText("Base.ouvrir: url = "+url);
			System.out.println("Base.ouvrir: url = "+url);
		} catch (SQLException e) {
			//GUI.getInstance().setConsolText("Base.ouvrir: erreur url = "+url);
			System.out.println("Base.ouvrir: erreur url = "+url+"\n");
			e.printStackTrace();
		}
	}
	
	public void fermer() {
		try {if (connection != null) connection.close();} catch (Exception e) {}
	}
		
	
	public String getPasswd(String login) {
		String passwd = "";
		
		String query = "select passwd from medecin where identifiant='"+login+"'";
		
		if (this.connection == null) return "";
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = (PreparedStatement) this.connection.prepareStatement(query);
			rs = ps.executeQuery();
			while (rs.next()) {
				passwd = rs.getString("passwd");
			}
			rs.close();
		} catch (SQLException e) {
			System.out.println("getPasswd: erreur "+e.getMessage());
		}
		try {
			if (ps != null) ps.close();
		} catch (Exception e) {}
		
		
		return passwd;
	}
	
	public ArrayList<Patient> listePatient() {
		ArrayList<Patient> res = new ArrayList<Patient>();
		
		String query = "select * from patient";
		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement)connection.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Patient l = new Patient(rs.getInt("id"),rs.getString("civilite"), rs.getString("nom"), rs.getString("prenom"), rs.getString("date_naissance"), rs.getInt("poids"), rs.getString("email"), rs.getString("commentaire"));
				res.add(l);
			}
			rs.close();
		} catch (SQLException e) {
			System.out.println("lister: erreur "+e.getMessage());
			res = null;
		}
		try {if (ps != null) ps.close();} catch (Exception e) {}
		
		return res;
	}
	
	public Patient selectPatient(int id) {
		Patient res = null;
		
		String query = "select * from patient where id="+id;
		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement)connection.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				res = new Patient(rs.getInt("id"),rs.getString("civilite"), rs.getString("nom"), rs.getString("prenom"), rs.getString("date_naissance"), rs.getInt("poids"), rs.getString("email"), rs.getString("commentaire"));
			}
			rs.close();
		} catch (SQLException e) {
			System.out.println("select: erreur "+e.getMessage());
			res = null;
		}
		try {if (ps != null) ps.close();} catch (Exception e) {}
		
		return res;
	}
	
	public boolean addPatient(Patient patient) {

		boolean res = true;
		String query = "INSERT INTO patient (civilite, nom, prenom, date_naissance, poids, email, commentaire) ";
		query += "VALUES (? , ? , ?, ?, ?, ?, ?)";
		PreparedStatement ps = null;

		try {
			ps = (PreparedStatement) this.getConnection().prepareStatement(
					query);
			ps.setString(1, patient.getCivilite());
			ps.setString(2, patient.getNom());
			ps.setString(3, patient.getPrenom());
			ps.setString(4, patient.getDate_naissance());
			ps.setInt(5, patient.getPoids());
			ps.setString(6, patient.getEmail());
			ps.setString(7, patient.getCommentaire());
			ps.executeUpdate();

		} catch (SQLException e) {
			System.out.println("ERREUR addPatient : " + e.getMessage());
			res = false;
		}

		try {
			if (ps != null)
				ps.close();
		} catch (Exception e) {

		}

		return res;

	}
	
	public boolean modPatient(int id, Patient patient) {
		
		boolean res = true;
		String query = "UPDATE patient SET civilite=?, nom=?, prenom=?, date_naissance=?, poids=?, email=?, commentaire=? ";
		query += "WHERE patient.id=?";
		PreparedStatement ps = null;

		try {
			ps = (PreparedStatement) this.getConnection().prepareStatement(query);
			ps.setString(1, patient.getCivilite());
			ps.setString(2, patient.getNom());
			ps.setString(3, patient.getPrenom());
			ps.setString(4, patient.getDate_naissance());
			ps.setInt(5, patient.getPoids());
			ps.setString(6, patient.getEmail());
			ps.setString(7, patient.getCommentaire());
			ps.setInt(8, id);
			ps.executeUpdate();

		} catch (SQLException e) {
			System.out.println("ERREUR modPatient : " + e.getMessage());
			res = false;
		}

		try {
			if (ps != null)
				ps.close();
		} catch (Exception e) {

		}

		return res;
	}
	
	public boolean dropPatient(int id)  {
		
		boolean res = true;
		String query = "DELETE FROM patient WHERE patient.id=?";
		PreparedStatement ps = null;
		
		try {
			ps = (PreparedStatement)this.getConnection().prepareStatement(query);
			ps.setInt(1,id);
			ps.executeUpdate();
			
		} catch (SQLException e) {
			System.out.println("ERREUR dropPatient : "+e.getMessage());
			res = false;
		}
		
		try {
			if (ps != null) ps.close();
		} catch (Exception e) {
			
		}
		
		return res;		
	}
	
	public boolean addOrdonnance(Ordonnance ordo)  {
		
		boolean res = true;
		String query = "INSERT INTO ordonnance (idPatient, idMedecin, date) VALUES (?, ?, ?)";
		PreparedStatement ps = null;
		
		String DATE_FORMAT_NOW = "dd-MM-yyyy HH:mm:ss";
		Calendar cal = Calendar.getInstance();
	    SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);
	    String date = sdf.format(cal.getTime());


		try {
			ps = (PreparedStatement) this.getConnection().prepareStatement(query);
			ps.setInt(1, ordo.getPatient().getIdPatient());
			ps.setInt(2, ordo.getMedecin().getId());
			ps.setString(3, date);
			ps.executeUpdate();

		} catch (SQLException e) {
			System.out.println("ERREUR addOrdonnance : " + e.getMessage());
			res = false;
		}

		try {
			if (ps != null)
				ps.close();
		} catch (Exception e) {

		}

		return res;
		
	}
	
	public ArrayList<OrdonnanceBDD> listOrdonnance() {
		
		ArrayList<OrdonnanceBDD> res = new ArrayList<OrdonnanceBDD>();

		String query = "select * from ordonnance";
		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement) connection.prepareStatement(query);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				OrdonnanceBDD m = new OrdonnanceBDD(rs.getInt("id"),rs.getInt("idPatient"), rs.getInt("idMedecin"), rs.getString("date"));
				res.add(m);
			}

			rs.close();
		} catch (SQLException e) {
			System.out.println("ERREUR listOrdonnaceBDD : " + e.getMessage());
			res = null;
		}
		try {
			if (ps != null) ps.close();
		} catch (Exception e) {
		}

		return res;
	}
	
	public ArrayList<OrdonnanceBDD> listOrdonnance(int from, int to) {
		
		ArrayList<OrdonnanceBDD> res = new ArrayList<OrdonnanceBDD>();

		String query = "select * from ordonnance ORDER BY date DESC LIMIT "+from+","+to;
		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement) connection.prepareStatement(query);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				OrdonnanceBDD m = new OrdonnanceBDD(rs.getInt("id"),rs.getInt("idPatient"), rs.getInt("idMedecin"), rs.getString("date"));
				res.add(m);
			}

			rs.close();
		} catch (SQLException e) {
			System.out.println("ERREUR listOrdonnaceBDD : " + e.getMessage());
			res = null;
		}
		try {
			if (ps != null) ps.close();
		} catch (Exception e) {
		}

		return res;
	}
	
	public int selectLastOrdo() {
		
		int res = -1;

		String query = "select max(id) as nume from ordonnance LIMIT 1";
		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement) connection.prepareStatement(query);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				res = rs.getInt("nume");
			}

			rs.close();
		} catch (SQLException e) {
			System.out.println("ERREUR select Last Ordonnance : " + e.getMessage());
			res = -1;
		}
		try {
			if (ps != null) ps.close();
		} catch (Exception e) {
		}

		return res;
	}
	
	public int selectNbOrdo() {
		
		int res = -1;

		String query = "select count(id) as nume from ordonnance";
		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement) connection.prepareStatement(query);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				res = rs.getInt("nume");
			}

			rs.close();
		} catch (SQLException e) {
			System.out.println("ERREUR select Last Ordonnance : " + e.getMessage());
			res = -1;
		}
		try {
			if (ps != null) ps.close();
		} catch (Exception e) {
		}

		return res;
	}
	
	
	public Medecin selectMedecin(String identifiant)  {
		
		Medecin res = null;

		String query = "select * from medecin where identifiant=?";
		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement) connection.prepareStatement(query);
			ps.setString(1, identifiant);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				res = new Medecin(rs.getInt("id"), rs.getString("identifiant"), rs.getString("nom"), rs.getString("prenom"),
									rs.getString("adresse"), rs.getInt("cp"), rs.getString("ville"), rs.getString("tel"),
									rs.getString("qualification"), rs.getString("num"));
			}

			rs.close();
		} catch (SQLException e) {
			System.out.println("ERREUR selectMedecin : " + e.getMessage());
			res = null;
		}
		try {
			if (ps != null)
				ps.close();
		} catch (Exception e) {
		}

		return res;
		
	}
	
	public Medecin selectMedecin(int id)  {
		
		Medecin res = null;

		String query = "select * from medecin where id=?";
		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement) connection.prepareStatement(query);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				res = new Medecin(rs.getInt("id"), rs.getString("identifiant"), rs.getString("nom"), rs.getString("prenom"),
									rs.getString("adresse"), rs.getInt("cp"), rs.getString("ville"), rs.getString("tel"),
									rs.getString("qualification"), rs.getString("num"));
			}

			rs.close();
		} catch (SQLException e) {
			System.out.println("ERREUR selectMedecin : " + e.getMessage());
			res = null;
		}
		try {
			if (ps != null)
				ps.close();
		} catch (Exception e) {
		}

		return res;
		
	}
	
	public String selectMedecinPwd(int id)  {
		
		String res = "";

		String query = "select passwd from medecin where id=?";
		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement) connection.prepareStatement(query);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				res = rs.getString("passwd");
			}

			rs.close();
		} catch (SQLException e) {
			System.out.println("ERREUR selectMedecinPwd : " + e.getMessage());
			res = null;
		}
		try {
			if (ps != null)
				ps.close();
		} catch (Exception e) {
		}

		return res;
		
	}
	
	
	public ArrayList<Medoc> listMedoc()  {
		
		ArrayList<Medoc> res = new ArrayList<Medoc>();

		String query = "select * from medicament";
		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement) connection.prepareStatement(query);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Medoc m = new Medoc(rs.getInt("id"),rs.getString("nom"), rs.getString("posologie"));
				res.add(m);
			}

			rs.close();
		} catch (SQLException e) {
			System.out.println("ERREUR listMedoc : " + e.getMessage());
			res = null;
		}
		try {
			if (ps != null) ps.close();
		} catch (Exception e) {
		}

		return res;
		
	}

	public Medoc selectMedoc(int id)  {
		
		Medoc res = null;

		String query = "select * from medicament where id=?";
		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement) connection.prepareStatement(query);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				res = new Medoc(rs.getInt("id"),rs.getString("nom"), rs.getString("posologie"));
			}

			rs.close();
		} catch (SQLException e) {
			System.out.println("ERREUR listMedoc : " + e.getMessage());
			res = null;
		}
		try {
			if (ps != null) ps.close();
		} catch (Exception e) {
		}

		return res;
		
	}

	
}
